package com.echo.dao;

import com.echo.entity.ArticleCategoryRelation;
import com.echo.util.JdbcUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 文章分类关系DAO类，负责文章和分类关系的数据库操作
 */
public class ArticleCategoryRelationDao {
    private static final Logger logger = LoggerFactory.getLogger(ArticleCategoryRelationDao.class);
    
    /**
     * 添加文章分类关系
     */
    public int insert(ArticleCategoryRelation relation) {
        String sql = "INSERT INTO article_category_relation (article_id, category_id) " +
                     "VALUES (?, ?)";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            stmt.setInt(1, relation.getArticleId());
            stmt.setInt(2, relation.getCategoryId());
            
            int rowsAffected = stmt.executeUpdate();
            if (rowsAffected > 0) {
                rs = stmt.getGeneratedKeys();
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            logger.error("添加文章分类关系失败: " + e.getMessage(), e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return 0;
    }
    
    /**
     * 批量添加文章分类关系
     */
    public int batchInsert(Integer articleId, List<Integer> categoryIds) {
        if (categoryIds == null || categoryIds.isEmpty()) {
            return 0;
        }
        
        String sql = "INSERT INTO article_category_relation (article_id, category_id) " +
                     "VALUES (?, ?)";
        Connection conn = null;
        PreparedStatement stmt = null;
        int count = 0;
        
        try {
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false); // 开始事务
            stmt = conn.prepareStatement(sql);
            
            System.out.println("=== 开始批量添加文章分类关系 ===");
            System.out.println("文章ID: " + articleId + ", 分类ID列表: " + categoryIds);
            for (Integer categoryId : categoryIds) {
                stmt.setInt(1, articleId);
                stmt.setInt(2, categoryId);
                stmt.addBatch();
                System.out.println("添加批处理：article_id=" + articleId + ", category_id=" + categoryId);
            }
            
            int[] results = stmt.executeBatch();
            // MySQL的JDBC驱动对批处理操作返回的是Statement.SUCCESS_NO_INFO(-2)而不是实际影响行数
            // 所以我们需要检查结果是否为-2(SUCCESS_NO_INFO)或大于0
            for (int result : results) {
                if (result == java.sql.Statement.SUCCESS_NO_INFO || result > 0) {
                    count++;
                }
            }
            System.out.println("批处理执行结果: " + Arrays.toString(results));
            System.out.println("成功统计数量: " + count);
            
            conn.commit(); // 提交事务
        } catch (SQLException e) {
            logger.error("批量添加文章分类关系失败: " + e.getMessage(), e);
            if (conn != null) {
                try {
                    conn.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    logger.error("事务回滚失败: " + ex.getMessage(), ex);
                }
            }
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true); // 恢复自动提交
                } catch (SQLException e) {
                    logger.error("恢复自动提交失败: " + e.getMessage(), e);
                }
            }
            JdbcUtils.close(conn, stmt); // 使用PreparedStatement参数版本的close方法
        }
        return count;
    }
    
    /**
     * 删除文章的所有分类关系
     */
    public int deleteByArticleId(Integer articleId) {
        String sql = "DELETE FROM article_category_relation WHERE article_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, articleId);
            
            return stmt.executeUpdate();
        } catch (SQLException e) {
            logger.error("删除文章分类关系失败: " + e.getMessage(), e);
        } finally {
            JdbcUtils.close(conn, stmt, null);
        }
        return 0;
    }
    
    /**
     * 删除分类的所有文章关系
     */
    public int deleteByCategoryId(Integer categoryId) {
        String sql = "DELETE FROM article_category_relation WHERE category_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, categoryId);
            
            return stmt.executeUpdate();
        } catch (SQLException e) {
            logger.error("删除分类文章关系失败: " + e.getMessage(), e);
        } finally {
            JdbcUtils.close(conn, stmt, null);
        }
        return 0;
    }
    
    /**
     * 根据文章ID查询关联的分类ID列表
     */
    public List<Integer> findCategoryIdsByArticleId(Integer articleId) {
        String sql = "SELECT category_id FROM article_category_relation WHERE article_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Integer> categoryIds = new ArrayList<>();
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, articleId);
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                categoryIds.add(rs.getInt("category_id"));
            }
        } catch (SQLException e) {
            logger.error("根据文章ID查询分类ID失败: " + e.getMessage(), e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return categoryIds;
    }
    
    /**
     * 根据分类ID查询关联的文章ID列表
     */
    public List<Integer> findArticleIdsByCategoryId(Integer categoryId) {
        String sql = "SELECT article_id FROM article_category_relation WHERE category_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Integer> articleIds = new ArrayList<>();
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, categoryId);
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                articleIds.add(rs.getInt("article_id"));
            }
        } catch (SQLException e) {
            logger.error("根据分类ID查询文章ID失败: " + e.getMessage(), e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return articleIds;
    }
    
    /**
     * 查询文章分类关系
     */
    public List<ArticleCategoryRelation> findRelationsByArticleId(Integer articleId) {
        String sql = "SELECT * FROM article_category_relation WHERE article_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<ArticleCategoryRelation> relations = new ArrayList<>();
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, articleId);
            rs = stmt.executeQuery();
            
            while (rs.next()) {
                relations.add(mapResultSetToRelation(rs));
            }
        } catch (SQLException e) {
            logger.error("查询文章分类关系失败: " + e.getMessage(), e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return relations;
    }
    
    /**
     * 检查文章是否关联了指定分类
     */
    public boolean existsRelation(Integer articleId, Integer categoryId) {
        String sql = "SELECT COUNT(*) FROM article_category_relation WHERE article_id = ? AND category_id = ?";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, articleId);
            stmt.setInt(2, categoryId);
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            logger.error("检查文章分类关系是否存在失败: " + e.getMessage(), e);
        } finally {
            JdbcUtils.close(conn, stmt, rs);
        }
        return false;
    }
    
    /**
     * 将ResultSet映射为ArticleCategoryRelation对象
     */
    private ArticleCategoryRelation mapResultSetToRelation(ResultSet rs) throws SQLException {
        ArticleCategoryRelation relation = new ArticleCategoryRelation();
        relation.setRelationId(rs.getInt("relation_id"));
        relation.setArticleId(rs.getInt("article_id"));
        relation.setCategoryId(rs.getInt("category_id"));
        
        return relation;
    }
}